﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]

public class Service : System.Web.Services.WebService
{
    private SqlConnection con;
    private SqlCommand comm;
    private DataSet ds;
    private SqlDataAdapter dap;
    public Service () {
        con = new SqlConnection("server=.;database=Test;uid=sa;pwd=");
        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod(Description = "Search Items")]
    public DataSet SearchItems(string ItemName)
    {
        comm = con.CreateCommand();
        comm.CommandText = "Select * from Item where ItemName like '%'+@name+'%'";
        comm.Parameters.AddWithValue("@name", ItemName);
        dap = new SqlDataAdapter(comm);
        con.Open();
        ds = new DataSet();
        dap.Fill(ds);
        return ds;
    }
    
    [WebMethod(Description = "Insert new Item")]
    public bool AddNewItem(string ItemName,float UnitPrice,string description)
    {
        con.Open();
        comm = con.CreateCommand();
        comm.CommandText = "Insert into Item values(@name,@price,@des)";
        comm.Parameters.AddWithValue("@name", ItemName);
        comm.Parameters.AddWithValue("@price", UnitPrice);
        comm.Parameters.AddWithValue("@des", description);
        if (comm.ExecuteNonQuery()>0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    [WebMethod(Description = "Update Items")]
    public bool UpdateItem(int ItemCode, string ItemName, float UnitPrice, string description)
    {
        con.Open();
        comm = con.CreateCommand();
        comm.CommandText = "Update Item set ItemName=@name,UnitPrice=@price,Desciption=@des where ItemCode=@code";
        comm.Parameters.AddWithValue("@code", ItemCode);
        comm.Parameters.AddWithValue("@name", ItemName);
        comm.Parameters.AddWithValue("@price", UnitPrice);
        comm.Parameters.AddWithValue("@des", description);
        if (comm.ExecuteNonQuery() > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    [WebMethod(Description = "Delete Items")]
    public bool DeleteItem(int ItemCode)
    {
        con.Open();
        comm = con.CreateCommand();
        comm.CommandText = "Delete Item where ItemCode=@code";
        comm.Parameters.AddWithValue("@code", ItemCode);
        if (comm.ExecuteNonQuery()>0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}